package com.witmed.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.security.auth.message.callback.PrivateKeyCallback.Request;
import javax.sql.rowset.CachedRowSet;

import org.apache.tomcat.jni.User;

import com.witmed.dao.AppointmentOperationDao;
import com.witmed.entity.Appointment;
import com.witmed.entity.Department;
import com.witmed.entity.Doctor;
import com.witmed.entity.Hospital;
import com.witmed.entity.UserInfo;
import com.witmed.util.DBUtils;
import com.witmed.util.PageInfo;

/**
 * 
 * @author ChenYouming AppointmentOperationDao接口的实现类
 */
public class AppointmentOperationDaoImpl implements AppointmentOperationDao {
	// 实例数据库工具类
	DBUtils db = new DBUtils();

	/**
	 * searchByDoctorNameOrDepatmentNameOrhosptialName接口的实现 typeid 是关键字类型 keyword
	 * 是关键字 pageNum 是页码 pageSize是页面记录数
	 */
	@Override
	public PageInfo<Doctor> searchByDoctorNameOrDepatmentNameOrhosptialName(int typeId, String keyword, int pageNum,
			int pageSize) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select")
		.append(" d.doctor_id,")
		.append("d.doctor_name,")
		.append("d.doctor_sex,")
				.append("d.doctor_pictuer_path,")
				.append("dp.department_name,")
				.append("h.hospital_name")
				.append(" from doctor d")
				.append(" right join department dp")
				.append(" on d.department_id = dp.department_id")
				.append(" right join hospital h")
				.append(" on dp.hospital_id =h.hospital_id");
		ResultSet resultSet = null;
		// 0是以医学姓名作为关键字查询
		if (typeId == 0) {
			stringBuffer.append(" where d.doctor_name like ? limit ?,?");
			resultSet = db.query(stringBuffer.toString(), keyword, (pageNum - 1) * pageSize, pageSize);
			// 1是以科室名字作为关键字查询
		} else if (typeId == 1) {
			stringBuffer.append(" where dp.department_name like ? limit ?,?");
			resultSet = db.query(stringBuffer.toString(), keyword, (pageNum - 1) * pageSize, pageSize);
			// 2是以医院名字作为关键字查询
		} else if (typeId == 2) {
			stringBuffer.append(" where h.hospital_name like ? limit ?,?");
			resultSet = db.query(stringBuffer.toString(), keyword, (pageNum - 1) * pageSize, pageSize);

		}
		List<Doctor> list = new ArrayList<Doctor>();
		// 遍历结果
		try {
			while (resultSet.next()) {
				Integer doctorId = resultSet.getInt("doctor_id");
				String doctorName = resultSet.getString("doctor_name");
				Integer doctorSex = resultSet.getInt("doctor_sex");
				String doctorPictuerPath = resultSet.getString("doctor_pictuer_path");
				String departmentName = resultSet.getString("department_name");
				String hospitalName = resultSet.getString("hospital_name");
				Hospital hospital = new Hospital(null, hospitalName);
				Department department = new Department(null, departmentName, hospital);
				Doctor doctor = new Doctor(doctorId, doctorName, null, doctorSex, null, null, doctorPictuerPath,
						department);
				list.add(doctor);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ResultSet resultSet1 = null;
		stringBuffer = new StringBuffer();
		stringBuffer.append("select")
		.append(" count(*)")
		.append(" from doctor d")
		.append(" join department dp")
		.append(" on d.department_id = dp.department_id")
		.append(" join hospital h")
		.append(" on dp.hospital_id =h.hospital_id");
		// 0是以医学姓名作为关键字查询
		if (typeId == 0) {
			stringBuffer.append(" where d.doctor_name like ? ");
			resultSet1 = db.query(stringBuffer.toString(), keyword);
			// 1是以科室名字作为关键字查询
		} else if (typeId == 1) {
			stringBuffer.append(" where dp.department_name like ?");
			resultSet1 = db.query(stringBuffer.toString(), keyword);
			// 2是以医院名字作为关键字查询
		} else if (typeId == 2) {
			stringBuffer.append(" where h.hospital_name like ?");
			resultSet1 = db.query(stringBuffer.toString(), keyword);

		}
		int total = 0;
		try {
			while (resultSet1.next()) {
				total = resultSet1.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Doctor> data = new PageInfo<>(list, pageNum, pageSize, total);

		return data;
	}

	/**
	 * 多条件组合查询分页所有
	 * 
	 * @param map 用于存放前端传的条件值
	 * @return 返回一个医生的分页工具类
	 */
	@Override
	public PageInfo<Doctor> searchByhosptialNameOrDepatmentNameOrDoctorNameAll(Map<String, Object> map) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select").append(" d.doctor_id,").append("d.doctor_name,").append("d.doctor_sex,")
				.append("d.doctor_pictuer_path,").append("dp.department_name,").append("h.hospital_name")
				.append(" from doctor d").append(" right join department dp")
				.append(" on d.department_id = dp.department_id").append(" right join hospital h")
				.append(" on dp.hospital_id =h.hospital_id");
		ResultSet resultSet = null;
		Object object = map.get("pageNum");
		String string = object.toString();
		int pageNum = Integer.parseInt(string);
		Object object1 = map.get("pageSize");
		String string1 = object1.toString();
		int pageSize = Integer.parseInt(string1);
		String hospitalName = map.get("hospitalName").toString();
		String departmentName = null;
		if (map.get("departmentName") == null) {
			stringBuffer.append(" where h.hospital_name =? limit ?,?");
			resultSet = db.query(stringBuffer.toString(), hospitalName, (pageNum - 1) * pageSize, pageSize);
		} else {
			departmentName = map.get("departmentName").toString();
			stringBuffer.append(" where h.hospital_name =? and dp.department_name =? limit ?,?");
			resultSet = db.query(stringBuffer.toString(), hospitalName, departmentName, (pageNum - 1) * pageSize,
					pageSize);
		}
		List<Doctor> list = new ArrayList<Doctor>();
		try {
			while (resultSet.next()) {
				Integer doctorId = resultSet.getInt("doctor_id");
				String doctorName = resultSet.getString("doctor_name");
				Integer doctorSex = resultSet.getInt("doctor_sex");
				String doctorPictuerPath = resultSet.getString("doctor_pictuer_path");
				departmentName = resultSet.getString("department_name");
				hospitalName = resultSet.getString("hospital_name");
				Hospital hospital = new Hospital(null, hospitalName);
				Department department = new Department(null, departmentName, hospital);
				Doctor doctor = new Doctor(doctorId, doctorName, null, doctorSex, null, null, doctorPictuerPath,
						department);
				list.add(doctor);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		stringBuffer = new StringBuffer();
		stringBuffer.append("select").append(" count(*)").append(" from doctor d").append(" join department dp")
				.append(" on d.department_id = dp.department_id").append(" join hospital h")
				.append(" on dp.hospital_id =h.hospital_id");
		resultSet = db.query(stringBuffer.toString());
		int total = 0;
		try {
			while (resultSet.next()) {
				total = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Doctor> data = new PageInfo<>(list, pageNum, pageSize, total);

		return data;

	}

	/**
	 * 实现findHospital接口类 所有查找医院
	 */
	@Override
	public List<Hospital> findHospital() {
		String sql = "select hospital_id,hospital_name from hospital";
		ResultSet resultSet = db.query(sql);
		List<Hospital> list = new ArrayList<Hospital>();
		try {
			while (resultSet.next()) {
				Integer hospitalId = resultSet.getInt("hospital_id");
				String hospitalName = resultSet.getString("hospital_name");
				Hospital hospital = new Hospital(hospitalId, hospitalName);
				list.add(hospital);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 实现findDepartment接口类 所有分组查找科室
	 */
	@Override
	public List<Department> findDepartment() {
		String sql = "select  department_name from  department GROUP BY department_name";
		ResultSet resultSet = db.query(sql);
		List<Department> list = new ArrayList<Department>();
		try {
			while (resultSet.next()) {
				String departmentName = resultSet.getString("department_name");
				Department department = new Department(null, departmentName, null);
				list.add(department);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 查询预约时间
	  * @return 返回预约时间集合
	 * @param time  时间
	 * @param doctorId   医生编号
	 * 
	 */
	@Override
	public List<Appointment> findAppointment(String time ,int doctorId) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select")
		.append(" a.appointment_id,")
		.append("a.appointment_time,")
		.append("d.doctor_id,")
		.append("d.doctor_name,")
		.append("u.user_id,")
		.append("u.user_name")
		.append(" from appointment a")
		.append(" left join doctor d on a.doctor_id = d.doctor_id")
		.append( " left join user u on a.user_id= u.user_id");
		//0是当天   1 为明天  3 为未来三天
		if ("0".equals(time)) {
			stringBuffer.append(" where DATEDIFF(appointment_time,NOW())=0 and a.doctor_id=?");
		}else if ("1".equals(time)) {
			stringBuffer.append(" where DATEDIFF(appointment_time,NOW())=1 and a.doctor_id=?");
		} else if ("3".equals(time)) {
			stringBuffer.append(" where DATEDIFF(appointment_time,NOW())>=0 AND DATEDIFF(appointment_time,NOW())<=3 and a.doctor_id=?");
		} 
		
		ResultSet resultSet=null;
		resultSet=db.query(stringBuffer.toString(),doctorId);
		List<Appointment> list =new ArrayList<Appointment>();
		try {
			while(resultSet.next()) {
				int appointmentId = resultSet.getInt(1);
				String appointmentTime =resultSet.getString(2);
				 doctorId =resultSet.getInt(3);
				 String doctorName =resultSet.getString(4);
				int userId=resultSet.getInt(5);
				String userName =resultSet.getString(6);
				Doctor doctor =new Doctor(doctorId, doctorName, null, null, null, null, null, null);
				UserInfo user =new UserInfo(userId, userName, null, null, null, null, null, null, null);
				Appointment appointment =new Appointment(appointmentId, appointmentTime, doctor, user);
				list.add(appointment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 新增预约时间表数据
	 * @param appointment 
	 * @return 影响行数
	 */
	@Override
	public int insert(Appointment appointment) {
		String sql = "insert into Appointment(appointment_id,appointment_time,doctor_id,user_id) values(null,?,?,null)";
		int i = db.update(sql,appointment.getAppointmentTime(),appointment.getDoctor().getDoctorId());
				return i;
	}
	/**
	 * 修改预约时间表数据
	 * @param appointment
	 * @return  影响行数
	 */
	@Override
	public int update(Appointment appointment) {
		int n = 0 ;
		if (appointment.getUser().getUserId()!=null) {
		n=db.update("update appointment set user_id=? where appointment_id=? and user_id is null", appointment.getUser().getUserId(),appointment.getAppointmentId());
		}else {
		n= db.update("update appointment set appointment_time = ?,doctor_id = ? where appointment_id = ? ",
				appointment.getAppointmentTime(),appointment.getDoctor().getDoctorId(),appointment.getAppointmentId());
	}
		return n;
		}
	/**
	 * 删除预约时间表数据
	 * @param appointmentid 预约时间id
	 * @return  影响行数
	 */
	@Override
	public int delete(int appointmentid) {
		return db.update("delete from appointment where appointment_id = ?", appointmentid);
	}
	/**
	 * 根据id查询预约时间
	 */
	@Override
	public List<Appointment> findAppointment(int appointmentId) {
		List<Appointment> list =new ArrayList<Appointment>();
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select")
		.append(" appointment_id,")
		.append("appointment_time")
		.append(" from appointment")
		.append(" where appointment_id=?");
		ResultSet resultSet=db.query(stringBuffer.toString(),appointmentId);
		try {
			while(resultSet.next()) {
				appointmentId = resultSet.getInt("appointment_id");
				String appointmentTime =resultSet.getString("appointment_time");
				Appointment appointment =new Appointment(appointmentId, appointmentTime, null, null);
				list.add(appointment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int updateDoctor(Appointment appointment) {
		int n = db.update("update appointment set appointment_time = ? where appointment_id = ?",
				appointment.getAppointmentTime(),appointment.getAppointmentId());
		return n;
	}

}
